********************************************************************************
* This the main file to generate regression tables on
* Industry level cash holding on SPPE and acquisition
********************************************************************************


clear
*cd "B:\Dropbox\compustat"  //enter working directory. You should change this line to the folder in your computer
cd "/Users/yuzhu/Desktop/codes_and_data_final_submisson/micro_level_evidence"

use "compustat2021.dta"

*===============================================================
* drop observations whose 
*===============================================================
sort gvkey fyear
drop if capx==. | aqc==. | sppe ==.
drop if fyear < 1971
drop if fyear>2018
by gvkey: gen id = 1 if fyear[_n]==fyear[_n-1]
replace id=0 if id==.
by gvkey: gen repid = sum(id)
drop if repid>0
* exclude financial firms
drop if sic>6000 & sic<6500
drop if sic<1000 | sic>9000 
*drop if sic >=4900 & sic<=4999

merge m:1 fyear using "liquidity_costs.dta" // merge with cpi data
drop if loc == "CAN"


******************************************
*generate higher level industry code
******************************************

/*gen bigsic = 1 if sic<1000
replace bigsic=2 if sic>=1000&sic<1500
replace bigsic=3 if sic>=1500&sic<1800
replace bigsic=4 if sic>=2000&sic<4000
replace bigsic=5 if sic>=4000&sic<5000
replace bigsic=6 if sic>=5000&sic<5200
replace bigsic=7 if sic>=5200&sic<6000
replace bigsic=8 if sic>=6000&sic<6800
replace bigsic=9 if sic>=7000&sic<9000
replace bigsic=10 if sic>=9000&sic<9730
replace bigsic=11 if sic>=9900&sic<10000
gen industry = "Agriculture" if bigsic==1
replace industry = "Mining" if bigsic==2
replace industry = "Construction" if bigsic==3
replace industry = "Manufacturing" if bigsic==4
replace industry = "Transportation" if bigsic==5
replace industry = "Whole Sale" if bigsic==6
replace industry = "Retail" if bigsic==7
replace industry = "Finance, Insurance and Real Estate" if bigsic==8
replace industry = "Services" if bigsic==9*/

gen bigsic = floor(sic/100)
gen sppe_at_ratio = sppe/at
drop if aqc<0
drop if sppe<0
*replace aqc = abs(aqc)
*replace sppe = abs(sppe)
*by gvkey: egen totalat = mean(at)
*by gvkey: gen idquantile = 1 if _n==1
*xtile ptile = totalat if idquantile==1,nq(100)
*by gvkey: replace ptile = ptile[1]
*gen fconstr=1 if ptile<=30
*replace fconstr=0 if ptile>=70
*replace fconstr=2 if ptile<70&ptile>30
replace cpi = cpi/100
gen che_asset = che/at
bysort fyear: egen total_che =sum(che)
bysort fyear: egen total_asset = sum(at)
************************************************
* Generate yearly industry level variables
************************************************

bysort state fyear: egen che_avg= mean(che)
by state fyear: egen ch_avg=mean(ch)
by state fyear: egen at_avg=mean(at)
by state fyear: egen sppe_avg= mean(sppe)
by state fyear: egen aqc_avg= mean(aqc)
by state fyear: egen capx_avg= mean(capx)
by state fyear: egen ebit_avg = mean(ebit)
by state fyear: egen sale_avg = mean(sale)
by state fyear: egen capxv_avg = mean(capxv)
by state fyear: egen dltt_avg = mean(dltt)
by state fyear: egen dlc_avg = mean(dlc)
by state fyear: egen seq_avg = mean(seq)
by state fyear: egen ebit_std = sd(ebit)
by state fyear: egen sale_std = sd(sale) 
by state fyear: egen at_std = sd(at) 
by state fyear: egen che_std = sd(che) 

************************************************
* assuming sppe sales occur within states
*
************************************************


by state fyear: gen dup=_n
drop if dup>1

*order industry che_ratio rel_ratio sppe_ratio aqc_ratio capx_ratio

*************************************************
* Normalize variables by cpi
*************************************************
gen at_r = at_avg/cpi    // _r means normalized to 1971 prices
gen che_r = log(che_avg/cpi)
gen ch_r = ch_avg/cpi
gen aqc_r = log(aqc_avg/cpi)
gen sale_r = log(sale_avg/cpi)
gen logat_r = log(at_r)
gen capx_r = log(capx_avg/cpi)
gen capxv_r = capxv_avg/cpi
gen sppe_r = log(sppe_avg/cpi)
gen ebit_r = log(ebit_avg/cpi)
gen ebit_std_r = ebit_std/cpi
gen sale_std_r = sale_std/cpi
gen at_std_r = at_std/cpi
gen che_std_r = che_std/cpi
gen logreal_cpi = log((aqc_avg+sppe_avg)/cpi_final)
*******************************************************
* normalize variable by total asset
*******************************************************
gen sppe_ratio = sppe_avg/(sppe_avg+aqc_avg)

gen cheat = log(che_avg/at_avg)
gen capxat = log(capx_avg/at_avg)
gen lratio = (dltt_avg+dlc_avg)/seq_avg
gen lratio_l = dltt_avg/seq_avg
gen lratio_c = dlc_avg/seq_avg
gen lratio_a = (at_avg- seq_avg)/seq_avg /* alternative calculation of leverage ratio: total liability/shareholders equity*/
gen capxvat = capxv_avg/at_avg  //capital expenditure on sppe
gen logat =log(at_avg)
gen logat_cpi_final = log(at_avg/cpi_final)
gen chat = ch/at_avg
gen aqcat = log(aqc_avg/at_avg)
gen sppeat = log(sppe_avg/at_avg)
gen saleat=log(sale_avg/at_avg)
gen ebitat= log(ebit_avg/at_avg)
gen logsppe_ratio = log(sppe_ratio)
gen logreal_at = log((aqc_avg+sppe_avg)/at_avg)
gen rshare = (aqc_avg+sppe_avg)/(aqc_avg+capx_avg)
gen logrshare = log(rshare)
 encode state, gen(state_id)
*replace lratio = lratio/10
xtset state_id fyear


estimates clear 

qui:xtreg logreal_at l.cheat l.ebitat l.logat_cpi_final l.lratio_c l.lratio_l l.capxat  , fe vce(robust)
estimates store m1

qui:xtreg logreal_at l.cheat l.ebitat  l.logat_cpi_final l.lratio_c  l.lratio_l l.capxat i.fyear , fe vce(robust)
estimates store m2


qui:xtreg logrshare l.cheat l.ebitat l.logat_cpi_final l.lratio_c l.lratio_l l.capxat  , fe vce(robust)
estimates store m3

qui:xtreg logrshare l.cheat l.ebitat  l.logat_cpi_final l.lratio_c  l.lratio_l l.capxat i.fyear , fe vce(robust)
estimates store m4


qui:xtreg logsppe_ratio l.cheat l.ebitat l.logat_cpi_final l.lratio_c l.lratio_l l.capxat , fe vce(robust)
estimates store m5

qui:xtreg logsppe_ratio l.cheat l.ebitat  l.logat_cpi_final l.lratio_c  l.lratio_l l.capxat i.fyear , fe vce(robust)
estimates store m6


esttab m1 m2 m3 m4 m5 m6 using "tables/state_sppe_ratio_all_at_cpi_log_final_v1.tex",  keep(L.cheat L.ebitat L.logat_cpi_final L.lratio_c L.lratio_l L.capxat) cells(b(star fmt(3)) se(par fmt(3))) stats(r2_a N)  starlevels(* 0.10 ** 0.05 *** 0.01) varlabels(L.cheat "Cash Holding" L.ebitat "Sales" L.logat_r "Log Asset" L.lratio_c "Leverage"  L.capxat "Capital Expenses" )  nonumbers mtitles("Reallocation" "Reallocation" "R Share" "R Share" "P Share" "P Share") addnotes("Robust standard errors are in brackets and are clustered at firm level.") legend replace

esttab m1 m2 m3 m4 m5 m6 using "tables/state_sppe_ratio_all_at_cpi_log_final_v1.csv",  keep(L.cheat L.ebitat L.logat_cpi_final L.lratio_c L.lratio_l L.capxat) cells(b(star fmt(3)) se(par fmt(3)))  stats(r2_a N) starlevels(* 0.10 ** 0.05 *** 0.01) varlabels(L.cheat "Cash Holding" L.ebitat "Sales" L.logat_r "Log Asset" L.lratio_c "Leverage" L.capxat "Capital Expenses" )  nonumbers mtitles("Reallocation" "Reallocation" "R Share" "R Share" "P Share" "P Share") addnotes("Robust standard errors are in brackets and are clustered at firm level.") legend replace









